//
//  DataBase.m
//  Lesson_SQL
//
//  Created by dlios on 15-8-25.
//  Copyright (c) 2015年 lanou3g.com 蓝鸥科技. All rights reserved.
//

#import "DataBase.h"

@implementation DataBase
+(DataBase *)shareDataBase
{
		static DataBase *dbHandle = nil;
		if (dbHandle == nil) {
    
				dbHandle =[[DataBase alloc]init];
		}
		return dbHandle;
}
static sqlite3 *db;//创建一个数据库对象

#pragma mark- 打开数据库

-(void)openDB
{
		//获取沙盒路径
		NSString *documentPath =[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
		//拼接sqlite文件路径
		NSString *sqlitePath =[documentPath stringByAppendingPathComponent:@"shoucang.sqlite"];
		NSLog(@"路径= %@",sqlitePath);
		if (db != nil) {
    
				NSLog(@"数据库已经打开");
				return;
		}
		//打开数据库的方法
		int result =sqlite3_open(sqlitePath.UTF8String, &db);
		if (result ==SQLITE_OK) {
    
				NSLog(@"数据库打开成功");
		}else
		{
				NSLog(@"数据库打开失败");
		}
		
}

#pragma mark- 创建数据库表

-(void)createTable
{
		//生成创建表的SQL语句  大写字母不用改  该小写字母的地方
		NSString *createsql =@"CREATE TABLE IF NOT EXISTS shoucang(number INTEGER PRIMARY KEY AUTOINCREMENT,title TEXT ,title_page TEXT,date_str TEXT,product_id TEXT,price TEXT)";
		//执行SQL语句
		int result =sqlite3_exec(db, createsql.UTF8String, NULL, NULL, nil);
		if (result == SQLITE_OK) {
    
				NSLog(@"创建表成功");
		}
		else
		{
				NSLog(@"创建表失败");
		}
}

#pragma mark- 插入信息  (增加)

- (void)insertStudent:(PlayModel *)model
{
		//创建插入学生信息的sql语句
		NSString *insertSql =[NSString  stringWithFormat:@"INSERT INTO shoucang(title,title_page,date_str,product_id,price) VALUES('%@','%@','%@','%@','%@')",model.title,model.title_page,model.date_str,model.product_id,model.price];
		//执行sql语句
		int result =sqlite3_exec(db,insertSql.UTF8String, NULL, NULL, nil);
		if (result == SQLITE_OK) {
				NSLog(@"插入信息成功");
		}
		else
		{
				NSLog(@"插入信息失败");
		}
}

#pragma mark- 更新信息  (修改)

-(void)updateStudent:(PlayModel *)model number:(NSInteger)number
{
		//创建跟新学生信息的sql语句
		NSString *updateSql =[NSString stringWithFormat:@"UPDATE lanou0722 SET title = '%@', title_page = '%@',date_str ='%@',product_id = '%@',price = '%@' WHERE number = '%ld'",model.title,model.title_page,model.date_str,model.product_id,model.price, number];
		//执行SQL语句
		int result =sqlite3_exec(db, updateSql.UTF8String, NULL, NULL, nil);
		if (result == SQLITE_OK) {
    
				NSLog(@"更新成功");
		}else
		{
				NSLog(@"更新失败");
		}
}

#pragma mark- 删除信息
-(void)deleteStudent:(NSString *)title
{
		//创建删除学生信息sql语句
		NSString *deleteSql =[NSString stringWithFormat:@"DELETE FROM shoucang WHERE title = '%@'",title];
		//执行sql语句
		int result =sqlite3_exec(db, deleteSql.UTF8String, NULL, NULL, nil);
		if (result == SQLITE_OK) {
    
				NSLog(@"删除成功");
				
		}
		else
		{
				NSLog(@"删除失败");
		}
}

#pragma mark- 查询信息

-(NSMutableArray *)selectAll
{
		NSString *selectSql =@"SELECT * FROM shoucang";
		//创建数据库跟随指针,用来循环遍历数据库表的每行数据
		sqlite3_stmt *stmt =nil;
		//参数1  数据库对象 参数2 查询语句 参数3 查询语句字数限制(-1为不限制) 参数4:跟随指针对象 参数5 nil
		int result =sqlite3_prepare_v2(db,selectSql.UTF8String, -1, &stmt, nil);
		//创建存放信息的大叔组
		NSMutableArray *array =[[NSMutableArray alloc] init];
		if (result == SQLITE_OK) {
   // NSLog(@"查询准备工作完成");
				
				//开始循环遍历查询数据库每一条数据
				while (sqlite3_step(stmt)== SQLITE_ROW) {
						
						//当跟随指针指向的行的数据符合你的查询条件,逐一取出每列数据
						int number =sqlite3_column_int(stmt, 0);
						const unsigned char *title =sqlite3_column_text(stmt, 1);
						const unsigned char *title_page =sqlite3_column_text(stmt, 2);
						const unsigned char *date_str =sqlite3_column_text(stmt, 3);
						const unsigned char *product_id =sqlite3_column_text(stmt, 4);
                        const unsigned char *price =sqlite3_column_text(stmt, 5);

						NSString *titleString =[NSString stringWithUTF8String:(const char *)title];
						
						NSString *title_pageString =[NSString stringWithUTF8String:(const char *)title_page];
						NSString *date_strString =[NSString stringWithUTF8String:(const char *)date_str];
						NSString *product_idString =[NSString stringWithUTF8String:(const char *)product_id];
                        NSString *priceString =[NSString stringWithUTF8String:(const char *)price];

						PlayModel *model =[[PlayModel alloc]init];
						model.title = titleString;
						model.number = number;
                        model.product_id =product_idString;
						model.title_page =title_pageString;
						model.date_str =date_strString;
                        model.price = priceString;
						[array addObject:model];

		}
		}
		else{
				NSLog(@"查询失败");
		}
		return array;
}

#pragma mark- 关闭数据库

-(void)closeDB
{
		db =nil;
		int result =sqlite3_close(db);
		if (result == SQLITE_OK	) {
				NSLog(@"关闭数据库成功");
    
		}else
		{
				NSLog(@"关闭数据库失败");
		}
}

#pragma mark- 删除表

-(void)dropTable
{
		NSString *dropSql =@"DROP TABLE shoucang";
		int result = sqlite3_exec(db, dropSql.UTF8String, NULL, NULL, nil);
		if (result == SQLITE_OK) {
    
				NSLog(@"删除表成功");
		}
		else
		{
				NSLog(@"删除表失败");
		}
}
@end
